package com.hc.android.research.persistence;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class ResearchDB extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 4;
	private static final String DATABASE_NAME = "research";
	public static final String DATABASE_ITEM_TABLE_NAME = "item";
	public static final String DATABASE_ITEM_HISTORY_TABLE_NAME = "itemHistory";
	public static final String DATABASE_SETTINGS_TABLE_NAME = "settings";
	
	public ResearchDB(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//Table for storing items names and urls
		db.execSQL("CREATE TABLE " + DATABASE_ITEM_TABLE_NAME + " (itemID integer primary key AUTOINCREMENT, itemName text UNIQUE, itemURL text)");
		//Table for storing the price at a given time
		db.execSQL("CREATE TABLE " + DATABASE_ITEM_HISTORY_TABLE_NAME + " (itemID integer primary key, itemPrice text, priceDate timestamp CURRENT_TIMESTAMP, FOREIGN KEY(itemID) REFERENCES "+DATABASE_ITEM_TABLE_NAME+"(itemID))");
		//table for storing the settings for the app
		db.execSQL("CREATE TABLE " + DATABASE_SETTINGS_TABLE_NAME + " (settingName text primary key, settingValue text)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {

		db.execSQL("DROP TABLE " + DATABASE_ITEM_HISTORY_TABLE_NAME);
		db.execSQL("CREATE TABLE " + DATABASE_ITEM_HISTORY_TABLE_NAME + " (itemID integer, itemPrice text, priceDate text, PRIMARY KEY(itemID, itemPrice) FOREIGN KEY(itemID) REFERENCES "+DATABASE_ITEM_TABLE_NAME+"(itemID))");
	}
	
}
